﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Security.Permissions;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Xml.Linq;
using Microsoft.Data.Sqlite;
using static Microsoft.EntityFrameworkCore.DbLoggerCategory.Database;

namespace homework5
{
    public class DatabaseHelper
    {
        public static readonly string DBFileName = "school.db";
        public static readonly string BaseDirectory = Path.GetFullPath(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "..", "..", "..", "Data"));
        public static readonly string DBFilePath = Path.GetFullPath(Path.Combine(BaseDirectory, DBFileName));


        public static void Log(string message, bool isSucessful = true, string filename = "GeneralLog.txt")
        {
            string logFilePath = Path.Combine(BaseDirectory, "logs", filename);
            Directory.CreateDirectory(Path.GetDirectoryName(path: logFilePath)); // Ensure the directory exists
            using (StreamWriter writer = new StreamWriter(logFilePath, true))
            {
                writer.WriteLine($"{DateTime.Now}: {message}");
            }
            if (isSucessful)
            {
                LogOperation(message);
            }
        }
        public static void LogOperation(string operation)
        {
            using (var connection = DatabaseHelper.GetDatabaseConnection())
            {
                connection.Open();
                using (var cmd = new SqliteCommand("INSERT INTO Log (Operation) VALUES (@operation)", connection))
                {
                    cmd.Parameters.AddWithValue("@operation", operation);
                    cmd.ExecuteNonQuery();
                }
            }
        }

        public static SqliteConnection GetDatabaseConnection(string operation = "connect",string databaseFilePath = null)
        {
            if (string.IsNullOrEmpty(databaseFilePath))
            {
                databaseFilePath = DBFilePath;
            }
            if (operation != "connect"&& operation!= "initial")
                Log($"尝试{operation}",false);
            try
            {
                if (!File.Exists(databaseFilePath) && operation!="initial")
                {
                    throw new FileNotFoundException($"Database file not found at path: {databaseFilePath}");
                }
                string connectionString = $"Data Source={databaseFilePath};Mode=ReadWriteCreate;";
                var connection = new SqliteConnection(connectionString);
                if(operation == "initial")
                {
                    Log($"初始化", false);
                }
                //connection.Open();
                return connection;
            }
            catch (Exception ex)
            {
                Log($"Failed to connect to the database file, DatabaseFilePath: {databaseFilePath}");
                throw new Exception($"Error connecting to the database file: {databaseFilePath}", ex);
            }
        }
        public void Add(string tableName, Dictionary<string, object> columnValues)
        {
            try
            {
                using (var connection = DatabaseHelper.GetDatabaseConnection())
                {
                    connection.Open();

                    // 构建列名和参数占位符的字符串
                    string columns = string.Join(", ", columnValues.Keys);
                    string parameters = string.Join(", ", columnValues.Keys.Select(key => "@" + key));

                    // 构建 SQL 插入语句
                    string query = $"INSERT INTO {tableName} ({columns}) VALUES ({parameters})";

                    using (var cmd = new SqliteCommand(query, connection))
                    {
                        // 添加参数值
                        foreach (var column in columnValues)
                        {
                            cmd.Parameters.AddWithValue("@" + column.Key, column.Value);
                        }

                        cmd.ExecuteNonQuery();
                    }
                }

                LogOperation($"Inserted into '{tableName}' with values: {string.Join(", ", columnValues.Select(kv => kv.Key + "=" + kv.Value))}");
            }
            catch (Exception ex)
            {
                Log(ex.Message, false);
            }
        }

        // 通用的 GetId 方法
        public int GetId(string tableName, string name)
        {
            try
            {
                int id;
                using (var connection = DatabaseHelper.GetDatabaseConnection())
                {
                    using (var cmd = new SqliteCommand($"SELECT Id FROM {tableName} WHERE Name = @name", connection))
                    {
                        cmd.Parameters.AddWithValue("@name", name);
                        id = Convert.ToInt32(cmd.ExecuteScalar());
                    }
                    LogOperation($"Searched ID in table '{tableName}' for name = '{name}'");
                }
                return id;
            }
            catch (Exception ex)
            {
                Log(ex.Message, false);
                return -1;
            }
        }
        public string GetName(string tableName, int id)
        {
            try
            {
                string  name;
                using (var connection = DatabaseHelper.GetDatabaseConnection())
                {
                    using (var cmd = new SqliteCommand($"SELECT Name FROM {tableName} WHERE Id = @id", connection))
                    {
                        cmd.Parameters.AddWithValue("@id", id);
                        var result = cmd.ExecuteScalar();
                        name = result == null ? null : Convert.ToString(result); // 如果找不到记录返回 null
                    }
                    LogOperation($"Searched Name in table '{tableName}' for Id = '{id}'");
                }
                return name;
            }
            catch (Exception ex)
            {
                Log(ex.Message, false);
                return "null";
            }
        }

        // 通用的 Delete 方法
        public void Delete(string tableName, string name)
        {
            try
            {
                using (var connection = DatabaseHelper.GetDatabaseConnection())
                {
                    using (var cmd = new SqliteCommand($"DELETE FROM {tableName} WHERE Name = @name", connection))
                    {
                        cmd.Parameters.AddWithValue("@name", name);
                        cmd.ExecuteNonQuery();
                    }
                }
                LogOperation($"Deleted from table '{tableName}' where name = '{name}'");
            }
            catch (Exception ex)
            {
                Log(ex.Message, false);
            }
        }

        // 通用的 Update 方法
        public void Update(string tableName, string oldName, string newName)
        {
            try
            {
                using (var connection = DatabaseHelper.GetDatabaseConnection())
                {
                    using (var cmd = new SqliteCommand($"UPDATE {tableName} SET Name = @newName WHERE Name = @oldName", connection))
                    {
                        cmd.Parameters.AddWithValue("@newName", newName);
                        cmd.Parameters.AddWithValue("@oldName", oldName);
                        cmd.ExecuteNonQuery();
                    }
                }
                LogOperation($"Updated name from '{oldName}' to '{newName}' in table '{tableName}'");
            }
            catch (Exception ex)
            {
                Log(ex.Message, false);
            }
        }
    }
    public class DatasetInitial : DatabaseHelper
    {
        // 构造函数，确保在使用前访问静态字段
        static bool IsDatabaseInitialized(string databaseFilePath)
        {
            return File.Exists(databaseFilePath);
        }
        static public void InitialDataset()
        {
            // 检查 UserManagementDB 是否已初始化
            if (!IsDatabaseInitialized(DatabaseHelper.DBFilePath))
            {
                DatabaseHelper.Log($"初始化{DatabaseHelper.DBFilePath}",false);
                CreateDatabase();
            }
        }
        private static void CreateDatabase()
        {
            string createTablesQuery = @"
                            CREATE TABLE School (
                            Id INTEGER PRIMARY KEY AUTOINCREMENT,
                            Name TEXT NOT NULL
                        );

                        CREATE TABLE Class (
                            Id INTEGER PRIMARY KEY AUTOINCREMENT,
                            SchoolId INTEGER NOT NULL,
                            Name TEXT NOT NULL,
                            FOREIGN KEY (SchoolId) REFERENCES School(Id)
                        );

                        CREATE TABLE Student (
                            Id INTEGER PRIMARY KEY AUTOINCREMENT,
                            ClassId INTEGER NOT NULL,
                            Name TEXT NOT NULL,
                            Age INTEGER NOT NULL,
                            FOREIGN KEY (ClassId) REFERENCES Class(Id)
                        );

                        CREATE TABLE Log (
                            Id INTEGER PRIMARY KEY AUTOINCREMENT,
                            Operation TEXT NOT NULL,
                            Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
                        );

                ";

            using (var connection = GetDatabaseConnection("initial"))
            {
                connection.Open();
                using (var command = new SqliteCommand(createTablesQuery, connection))
                {
                    command.ExecuteNonQuery();
                }
                connection.Close();
            }
        }
    }
    public class Conditionsearch : DatabaseHelper
    {
        public static int GetClassCountBySchoolId(int schoolId)
        {
            try
            {
                using (var connection = GetDatabaseConnection())
                {
                    connection.Open();
                    using (var cmd = new SqliteCommand("SELECT COUNT(*) FROM Class WHERE SchoolId = @schoolId", connection))
                    {
                        cmd.Parameters.AddWithValue("@schoolId", schoolId);
                        int classCount = Convert.ToInt32(cmd.ExecuteScalar());
                        LogOperation($"查询学校 ID {schoolId} 的班级数: {classCount}");
                        return classCount;
                    }
                }
            }
            catch (Exception ex)
            {
                Log(ex.Message, false);
                return -1;
            }
        }

        public static int GetStudentCountBySchoolId(int schoolId)
        {
            try
            {
                using (var connection = GetDatabaseConnection())
                {
                    connection.Open();
                    using (var cmd = new SqliteCommand(
                        "SELECT COUNT(*) FROM Student WHERE ClassId IN (SELECT Id FROM Class WHERE SchoolId = @schoolId)",
                        connection))
                    {
                        cmd.Parameters.AddWithValue("@schoolId", schoolId);
                        int studentCount = Convert.ToInt32(cmd.ExecuteScalar());
                        LogOperation($"查询学校 ID {schoolId} 的学生数: {studentCount}");
                        return studentCount;
                    }
                }
            }
            catch (Exception ex)
            {
                Log(ex.Message, false);
                return -1;
            }
        }

        public static int GetStudentCountByClassId(int classId)
        {
            try
            {
                using (var connection = GetDatabaseConnection())
                {
                    connection.Open();
                    using (var cmd = new SqliteCommand("SELECT COUNT(*) FROM Student WHERE ClassId = @classId", connection))
                    {
                        cmd.Parameters.AddWithValue("@classId", classId);
                        int studentCount = Convert.ToInt32(cmd.ExecuteScalar());
                        LogOperation($"查询班级 ID {classId} 的学生数: {studentCount}");
                        return studentCount;
                    }
                }
            }
            catch (Exception ex)
            {
                Log(ex.Message, false);
                return -1;
            }
        }
        private static int CountByForeignKey(string table, string conditionColumn, int key)
        {
            try
            {
                using (var connection = DatabaseHelper.GetDatabaseConnection())
                {
                    string query = $"SELECT COUNT(*) FROM {table} WHERE {conditionColumn} = @key";
                    using (var cmd = new SqliteCommand(query, connection))
                    {
                        cmd.Parameters.AddWithValue("@key", key);
                        return Convert.ToInt32(cmd.ExecuteScalar());
                    }
                }
            }
            catch (Exception ex)
            {
                DatabaseHelper.Log(ex.Message, false);
                return -1;
            }
        }
    }
    interface IDatabase<T>
    {
        void Add(T entity);
        public void Delete(string name);
        public void Update(string name, string data);
        //public string search(string quary);
        public int GetId(string name);
        string ConditionSearch(string query, object type);
    }
    internal class SchoolDB : DatabaseHelper, IDatabase<School>
    {
        public void Add(School entity)
        {
           Add("School", new Dictionary<string, object>
            {
                { "Name", entity.Name }
               
            });
        }
        public int GetId(string name)
        {
            return GetId("School", name);
        }
        public void Delete(string name)
        {
            Delete("School", name);
        }
        public void Update(string name,string data)
        {
            Update("School",name, data);
        }

        public enum Type
        {
            GETID,
            GETCLASS,
            GETSTUDENT,
            UNDEFINED
        }
        public string ConditionSearch(string query, object type)
        {
            if (type is Type searchType) // 检查 type 是否为 SchoolDB.Type 枚举类型
            {
                string result="null";
                try
                {
                    switch (searchType)
                    {
                        case Type.GETID:
                            result=GetId(query).ToString();
                            break;
                        case Type.GETCLASS:
                            {
                                int schoolid = GetId(query);
                                int count= Conditionsearch.GetClassCountBySchoolId(schoolid);
                                result= count.ToString();
                            }
                            break;
                        case Type.GETSTUDENT:
                            {
                                int schoolid = GetId(query);
                                int count = Conditionsearch.GetStudentCountByClassId(schoolid);
                                result = count.ToString();
                            }
                            // 如果有 GETSCHOOL 的逻辑可以在这里实现
                            break;
                        default:
                            throw new Exception("无效的查询类型");
                    }
                    return result;
                }
                catch (Exception ex)
                {
                    Log(ex.Message, false);
                    return result;
                }
            }
            else
            {
                throw new ArgumentException("提供的类型无效，无法执行 ConditionSearch");
            }
        }
    }
    internal class ClassDB : DatabaseHelper, IDatabase<Class>
    {
        public void Add(Class entity)
        {
            Add("Class", new Dictionary<string, object>
            {
                { "Name", entity.Name },
                { "SchoolId",entity.SchoolId }
            }); 
        }
        public int GetId(string name)
        {
            return GetId("Class", name);
        }
        public void Delete(string name)
        {
            Delete("Class", name);
        }
        public void Update(string name, string data)
        {
            Update("Class", name, data);
        }
        public enum Type//需要实现，查学生，
        {
            GETID,
            GETSTUDENT,
            GETSCHOOLNAME,
            UNDEFINED
        }
        public string GetSchoolName(string classname)
        {
            try
            {
                
                int schoolid;
                using (var connection = GetDatabaseConnection())
                {
                    connection.Open();
                    using (var cmd = new SqliteCommand("SELECT SchoolId FROM Class WHERE Name = @classname", connection))
                    {
                        cmd.Parameters.AddWithValue("@classId", classname);
                        schoolid = Convert.ToInt32(cmd.ExecuteScalar());
                    }
                }
                var result=  GetName("School", schoolid);
                string schoolname =result==null?null:Convert.ToString(result);
                LogOperation($"查询班级 {classname} 的学校: {schoolname}");
                return schoolname;

            }
            catch (Exception ex)
            {
                Log(ex.Message, false);
                return null;
            }
            
        }
        public string ConditionSearch(string query, object type)
        {
            if (type is Type searchType) 
            {
                string result = "null";
                try
                {
                    switch (searchType)
                    {
                        case Type.GETID:
                            result = GetId(query).ToString();
                            break;
                        case Type.GETSTUDENT:
                            {
                                int classid = GetId(query);
                                int count = Conditionsearch.GetStudentCountByClassId(classid);
                                result = count.ToString();
                            }
                            break;
                        case Type.GETSCHOOLNAME:
                            {
                                result = GetSchoolName(query);
                            }
                            break;
                        
                        default:
                            throw new Exception("无效的查询类型");
                    }
                    return result;
                }
                catch (Exception ex)
                {
                    Log(ex.Message, false);
                    return result;
                }
            }
            else
            {
                throw new ArgumentException("提供的类型无效，无法执行 ConditionSearch");
            }
        }

    }
    internal class StudentDB : DatabaseHelper, IDatabase<Student> 
    {
        public void Add(Student entity)
        {
            Add("Student", new Dictionary<string, object>
            {
                { "Name", entity.Name },
                { "ClassId",entity.Id },
                { "Age",entity.Age },
            });
        }
        public int GetId(string name)
        {
            return GetId("Student", name);
        }
        public void Delete(string name)
        {
            Delete("Student", name);
        }
        public void Update(string name, string data)
        {
            Update("Student", name, data);
        }
        public enum Type//需要实现，查班级，学校（向上）
        {
            GETID,
            GETCLASSNAME,
            GETSCHOOLNAME,
            UNDEFINED
        }
        public string GetSchoolName(string StudentName)
        {
            try
            {

                int schoolid;
                using (var connection = GetDatabaseConnection())
                {
                    connection.Open();
                    using (var cmd = new SqliteCommand("SELECT SchoolId FROM Class WHERE id =(SELECT ClassId FROM Student WHERE Name = @StudentName)", connection))
                    {
                        cmd.Parameters.AddWithValue("@StudentName", StudentName);
                        schoolid = Convert.ToInt32(cmd.ExecuteScalar());
                    }
                }
                var result = GetName("School", schoolid);
                string schoolname = result == null ? null : Convert.ToString(result);
                LogOperation($"查询学生 {StudentName} 的学校: {schoolname}");
                return schoolname;

            }
            catch (Exception ex)
            {
                Log(ex.Message, false);
                return null;
            }

        }
        public string GetClassName(string StudentName)
        {
            try
            {

                int classid;
                using (var connection = GetDatabaseConnection())
                {
                    connection.Open();
                    using (var cmd = new SqliteCommand("SELECT ClassId FROM Student WHERE Name = @StudentName", connection))
                    {
                        cmd.Parameters.AddWithValue("@StudentName", StudentName);
                        classid = Convert.ToInt32(cmd.ExecuteScalar());
                    }
                }
                var result = GetName("Class", classid);
                string classname = result == null ? null : Convert.ToString(result);
                LogOperation($"查询学生 {StudentName} 的班级: {classname}");
                return classname;

            }
            catch (Exception ex)
            {
                Log(ex.Message, false);
                return null;
            }

        }
        public string ConditionSearch(string query, object type)
        {
            if (type is Type searchType)
            {
                string result = "null";
                try
                {
                    switch (searchType)
                    {
                        case Type.GETID:
                            result = GetId(query).ToString();
                            break;
                        case Type.GETCLASSNAME:
                            {
                                result = GetClassName(query);
                            }
                            break;
                        case Type.GETSCHOOLNAME:
                            {
                                result =GetSchoolName(query);
                            }
                            break;

                        default:
                            throw new Exception("无效的查询类型");
                    }
                    return result;
                }
                catch (Exception ex)
                {
                    Log(ex.Message, false);
                    return result;
                }
            }
            else
            {
                throw new ArgumentException("提供的类型无效，无法执行 ConditionSearch");
            }
        }
    }

}
